package cn.tedu.egmybatis2.mapper;

import cn.tedu.egmybatis2.pojo.entity.Customers;
import cn.tedu.egmybatis2.pojo.entity.UserOrders;
import cn.tedu.egmybatis2.pojo.vo.CustomersVO1;
import cn.tedu.egmybatis2.pojo.vo.CustomersVO2;
import cn.tedu.egmybatis2.pojo.vo.CustomersVO3;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface CustomersMapper {
    /**
     * 第1题：在客户信息表中插入一条数据：'0004', '13811112222', 'Tony', '成都'
     */
    @Insert("INSERT INTO mybatisdb.customers(cust_id,cust_tel,cust_name,address) " +
            "VALUES(#{customerId},#{customerTel},#{customerName},#{address})")
    int insertCustomer(Customers customer);

    /**
     * 第2题：在订单表中插入一条数据：
     * '202301020004', '0004', new Date(), '3', 4, 800.88
     */
    @Insert("INSERT INTO mybatisdb.user_orders(order_id,cust_id,order_date,status,products_num,amt) " +
            "VALUES(#{orderId},#{customerId},#{orderDate},#{status},#{productsNum},#{amt})")
    int insertUserOrder(UserOrders userOrder);

    /**
     * 第3题：根据客户编号查询某个客户的 姓名和电话，结果：CustomersVO1
     */
    @Select("SELECT cust_name customerName,cust_tel customerTel FROM mybatisdb.customers " +
            "WHERE cust_id=#{customerId}")
    CustomersVO1 selectCustomerById(String customerId);

    /**
     * 第4题：查询指定城市的客户的 姓名和地址，结果集放到 List集合中，
     * 比如查询北京的客户信息：CustomersVO2
     */
    @Select("SELECT cust_name customerName,address FROM mybatisdb.customers " +
            "WHERE address=#{address}")
    List<CustomersVO2> selectCustomersByAddress(String address);

    /**
     * 第5题[选做]：查询指定客户[编号]所成交的订单信息，
     * 将结果放到 List 集合中CustomersVO3
     * 显示 客户姓名、订单编号、订单金额、订单状态
     * 提示：使用连接查询
     */
    @Select("SELECT cust_name customerName,order_id orderId,amt,status " +
            "FROM mybatisdb.customers,mybatisdb.user_orders " +
            "WHERE customers.cust_id=user_orders.cust_id " +
            "AND customers.cust_id=#{customerId}")
    List<CustomersVO3> selectOrderInfo(String customerId);

}
